CREATE FUNCTION [dbo].[asi_Get_TransactionID](@Id varchar(10),
@TransReq varchar(10) )
--
-- Returns Transaction ID of the first, last or next last iMIS transaction
-- for the contact identified by @Id
--
-- Which transaction we require is identifed by the @TransReq parameter which may be one of
-- FIRST, LAST, NEXTLAST
--
-- This is used by Giving Summary display
--
RETURNS int AS
BEGIN

DECLARE @TransId int
DECLARE @FirstTransId int
DECLARE @FirstTransDate datetime
DECLARE @LastTransId int
DECLARE @LastTransDate datetime
DECLARE @NextLastTransId int
DECLARE @NextLastTransDate datetime
SET @TransId= 0
SET @LastTransId = 0
SET @NextLastTransId = 0
SET @FirstTransId = 0


-- Summarises the vGift rows - this takes reversals and so on into account
DECLARE @Gifts TABLE
(
  [OriginalTransaction] int,
  [Amount] money,
  [MinTransDate] datetime,  
  [MaxTransDate] datetime,
  [TransDate] datetime
)

INSERT INTO @Gifts( [OriginalTransaction], [Amount],
  [MinTransDate], [MaxTransDate]  )
SELECT [OriginalTransaction], SUM([Amount]),
       MIN( [TransactionDate] ),
       MAX( [TransactionDate] )
  FROM [vGift] GH1 WITH(NOLOCK)
WHERE [ID] =  @Id
GROUP BY [OriginalTransaction]  

-- remove transactions that have been reversed out to 0
DELETE FROM @Gifts WHERE [Amount] = 0

SELECT TOP 1 @FirstTransId = [OriginalTransaction],
             @FirstTransDate = [MinTransDate]
  FROM @Gifts
ORDER BY [MinTransDate], [OriginalTransaction]                            

SELECT TOP 1 @LastTransId = ISNULL([OriginalTransaction] ,0),
             @LastTransDate = [MaxTransDate]
  FROM @Gifts
  WHERE [OriginalTransaction] <> @FirstTransId
  AND [MaxTransDate] >= @FirstTransDate
ORDER BY [MaxTransDate] DESC, [OriginalTransaction] DESC                           

SELECT TOP 1 @NextLastTransId = ISNULL([OriginalTransaction] ,0)              
  FROM @Gifts
  WHERE [OriginalTransaction] <> @FirstTransId
  AND [OriginalTransaction] <> @LastTransId
  AND [MaxTransDate] >= @FirstTransDate
  AND [MaxTransDate] <= @LastTransDate
ORDER BY [MaxTransDate] DESC, [OriginalTransaction] DESC

IF (@TransReq = 'LAST')  -- most recent transaction   
BEGIN
   SET @TransId = @LastTransId     
END

IF @TransReq = 'FIRST'  -- oldest transaction
BEGIN
   SET @TransId = @FirstTransId  
   
END

IF (@TransReq = 'NEXTLAST') -- next to most recent transaction
BEGIN
  SET @TransId = @NextLastTransId                                   
END

RETURN @TransId
END






GO